# encoding: utf-8
# A29 节日祝福推送结果生成器
# author yeyi
# date 2021-02-08

import traceback
import json
import time
import datetime
import pymongo
import csv
import pymysql


######################## 配置文件配置说明（以下所有数据库尽量使用只读账号），根据环境自行配置
# 使用方法：
# 将脚本置于可以访问到如上配置的机器中（如果云桌面或生产跳板机）
# python 直接执行即可。会在当前目录下按当前时间生成执行日志，按日期及 taskId 生成推送结果 csv 文档。

# 要查找的 taskId,对应机车运营配置系统一个推送
# （即 user-push mysql 数据库表 db_userpush.push_task 的 id）列表
# 可以写多个如 taskIds = ['2883','2899']
taskIds = ['309','310','311','312','313','314']

# iov-notification 项目 mongo 数据库
# 预发布 
# notificationMongoDb = 'mongodb://mongouser:pretce%40guangqi@10.123.2.239:27017/diagnosis?authSource=admin'
# 生产
notificationMongoDb = 'mongodb://mongouser:tce%40guangqi2019@10.123.35.10:27017/admin'
#notificationMongoDb = 'mongodb://mongodb_rw:guangqi%402018@10.2.5.10:27017/'

# user-push 项目 mongo 数据库
# 预发布
# userPushMongoDb = 'mongodb://mongouser:pretce%40guangqi@10.123.2.239:27017/public-push?authSource=admin'
# 生产
userPushMongoDb = 'mongodb://mongouser:tce%40guangqi2019@10.123.19.2:27017'
# userPushMongoDb = 'mongodb://mongodb_rw:guangqi%402018@10.2.5.10:27017/'

# bss 项目 mysql 数据库登录信息
# sqlHost = '10.2.3.5'
# sqlPort = 3306
# sqlUser = 'root'
# sqlPasswd = 'sirun@2018'
# 预发布
# sqlHost = '10.123.2.10'
# sqlPort = 3306
# sqlUser = 'sirun'
# sqlPasswd = 'sirun@2019'
# 生产
sqlHost = '10.123.16.94'
sqlPort = 3306
sqlUser = 'sirun2'
sqlPasswd = '123^54Sirun'



# 输出文件名
fileName = 'pushResult'
fileNameTimeStr = time.strftime('%Y-%m-%d_%H-%M-%S',time.localtime(time.time()))
logFileName = fileName + '_' + fileNameTimeStr


# 任务开始时间
#startTime = '2021-02-08 11:00:00'
# 任务结束时间
#endTime = '2021-02-08 22:00:00'

# 统一处理异常
def dealException(e):
    f = open(logFileName+'.log', 'a+', encoding='utf-8')
    traceback.print_exc(file=f)
    print(str(e),file=f)
    f.close()

try:
    db = pymysql.connect(host=sqlHost,
                     port=sqlPort,
                     user=sqlUser,
                     passwd=sqlPasswd,
                     db='bss-gq',
                     charset='utf8')
    cursor = db.cursor(cursor=pymysql.cursors.DictCursor)  # 使用cursor()方法获取操作游标
except Exception as e:
    dealException(e)


def getVinsByDins(dins):
    # SQL 查询语句
    sql = "SELECT vi.vin,vi.din FROM tab_vehicle_info vi WHERE vi.din IN %s ORDER BY vi.din DESC"

    # 执行SQL语句
    cursor.execute(sql, (dins,))
    # 获取一条记录列表
    return cursor.fetchall()

def printt(s):
    f = open(logFileName+'.log', 'a+', encoding='utf-8')
    print(str(s),file=f)
    f.close()
    print(s)



def isDinInFailure(vin, failures):
    for f in failures:
        if vin==f['account']:
            return f

    return None

def getDin(dinVins, din):
    for d in dinVins:
        if int(din)==d['din']:
            return d['vin']
    return None

def findByTaskId(taskId):
    notifiMongo = pymongo.MongoClient(notificationMongoDb)
    notifDb = notifiMongo['iov-notification-record']
    notifTable = notifDb['record']
    global notifDatas
    notifDatas = dict()
    pushTitle = ''
    # 生成往 im 推送结果到 notifDatas 中
    notifData = notifTable.find({'taskId':taskId})
    notifNum = 0
    for i in notifData:
        notifNum = notifNum+1
        toUsers = i['toUsers']
        failures = dict()
        if 'failures' in i:
            failures = i['failures']
        result = i['result']
        #printt('notifTabl find: '+ taskId+', result: '+str(result)+', send: '+str(len(toUsers))+', err: '+str(len(failures)))
        vinDins = getVinsByDins(toUsers)
        if None == vinDins or 0==len(vinDins):
            printt('getVinsByDin empty: '+str(i['_id']))
            continue
        # 全部成功
        if 0==result:
            for din in toUsers:
                vin = getDin(vinDins, din)
                if None==vin:
                    printt('getDi1 empty: '+din)
                    continue
                notifData = dict()
                notifData['result'] =  '发送成功'
                notifDatas[vin] = notifData

        # 部分成功
        elif 1==result:
            for din in toUsers:
                vin = getDin(vinDins, din)
                if None==vin:
                    printt('getDi2 empty: '+din)
                    continue
                notifData = dict()
                f = isDinInFailure(din, failures)
                if None==f:
                    notifData['result'] =  '发送成功'
                else:
                    notifData['result'] =  '发送失败'
                    notifData['errCode'] = f['errCode']
                    notifData['errMsg'] = f['errMsg']
                    content = json.loads(i['content'])
                    if ''==pushTitle:
                        pushTitle = content['title']
                notifDatas[vin] = notifData
        # 全部失败
        elif 2==result:
            for din in toUsers:
                vin = getDin(vinDins, din)
                if None==vin:
                    printt('getDi3 empty: '+din)
                    continue
                notifData = dict()
                notifData['result'] =  '发送失败'
                notifData['errCode'] = '-1'
                notifDatas[vin] = notifData
        else:
            printt('result err: '+str(result)+', '+i['_id'])

    if 0==notifNum:
        printt('iov-notification-record mongo 查不到数据 taskId: '+taskId)

    userPushMongo = pymongo.MongoClient(userPushMongoDb)
    userPushDb = userPushMongo['user_push']
    userPushTable = userPushDb['push_record']
    # startDate = int(time.mktime(datetime.datetime.strptime(startTime, '%Y-%m-%d %H:%M:%S').timetuple())) * 1000
    # endDate = int(time.mktime(datetime.datetime.strptime(endTime, '%Y-%m-%d %H:%M:%S').timetuple())) * 1000
    # userPushDatas = userPushTable.find({'pushTaskId':taskId,'createTime':{'$gt':startDate,'$lt':endDate}})
    userPushDatas = userPushTable.find({'pushTaskId':taskId})
    try:
        with open(fileName+'_'+taskId+'_'+fileNameTimeStr+'.csv', 'w') as csvfile:
            writer = csv.writer(csvfile, lineterminator='\n')
            writer.writerow(['taskId', '消息标题', 'vin', '车系', '结果', '错误码', '错误说明'])
            userPushNum = 0
            for i in userPushDatas:
                userPushNum = userPushNum+1
                toPushVins = i['vins']
                printt('userPushTabl find: '+ taskId+', '+i['projectCode']+', '+str(len(toPushVins)))
                for vin in toPushVins:
                    printData = dict()
                    printData['errCode'] = None
                    printData['errMsg'] = None
                    printData['projectCode'] = i['projectCode']
                    if vin in notifDatas:
                        notifResult = notifDatas[vin]['result']
                        if '发送成功'==notifResult:
                            printData['result'] = '发送成功'
                        else:
                            printData['result'] = '发送失败'
                            printData['errCode'] = notifDatas[vin]['errCode']
                            printData['errMsg'] = notifDatas[vin]['errMsg']
                    else:
                        printData['result'] = '未发送'

                    writer.writerow([taskId, pushTitle, vin, printData['projectCode'], printData['result'], printData['errCode'], printData['errMsg']])

            if 0==userPushNum:
                printt('user-push mongo 查不到数据 taskId: '+taskId)
    except Exception as e:
        dealException(e)
        

# main start
printt('start: '+time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())))

try:
    printt('notificationMongoDb: '+notificationMongoDb)
    printt('userPushMongoDb: '+userPushMongoDb)
    printt('taskIds: '+str(taskIds))
    # printt('startTime: '+startTime)
    # printt('endTime: '+endTime)
    printt('sqlHost: '+sqlHost)
    printt('sqlPort: '+str(sqlPort))
    printt('sqlUser: '+sqlUser)
    printt('sqlPasswd: '+sqlPasswd)

    for id in taskIds:
        findByTaskId(id)
except Exception as e:
    dealException(e)
finally:
    db.close()

printt('end: '+time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())))